package com.yulian.repository.supplier_management;

import java.util.Iterator;

import com.healthmarketscience.sqlbuilder.CustomSql;
import com.healthmarketscience.sqlbuilder.InCondition;
import com.healthmarketscience.sqlbuilder.SelectQuery;

import io.netty.util.internal.logging.InternalLogger;
import io.netty.util.internal.logging.Log4JLoggerFactory;
import io.vertx.core.Future;
import io.vertx.core.Promise;
import io.vertx.core.json.JsonObject;
import io.vertx.sqlclient.Row;
import io.vertx.sqlclient.RowSet;
import io.vertx.sqlclient.SqlConnection;
import io.vertx.sqlclient.Tuple;

public class GoodsPriceBidParameterRepositoryImpl {

    final InternalLogger logger = Log4JLoggerFactory.getInstance(GoodsPriceBidParameterRepositoryImpl.class);

    /**
     * 获取记录总数
     *
     * @param conn
     * @param queryJSObj
     * @return
     */
    public Future<RowSet<Row>> getRecordsTotal(SqlConnection conn, JsonObject queryJSObj) {
        Promise<RowSet<Row>> promise = Promise.promise();
        //首先取出符合条件的总的记录数
        String sql = this.creaStrGetRecordsTotalWithJson(queryJSObj);
        conn
        .preparedQuery(sql)
        .execute(ar2 -> {
            //Release the connection to the pool
            //conn.close();
            if (ar2.succeeded()) {
                promise.complete(ar2.result());
            } else {
                logger.info("getRecordsTotal, query Failed");
                //这里就相当于继续向上抛出异常，用Promise来向上抛异常
                promise.fail(ar2.cause());
            }
        });
        return promise.future();
    }

    /**
     * 创建查询符合搜索条件的记录数的 sql语句
     *
     * @param queryJS
     * @return
     */
    public String creaStrGetRecordsTotalWithJson(JsonObject queryJS) {

        // define SelectQuery object
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql(" count(*) as total "))
        .addCustomFromTable("goods_price_bid_parameter");

        //common part to add InCondition
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
//						  logger.info(key+ ": "+ value.toString());
                if (!value.equals("") && !value.equals("[]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    //添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }

            } catch (Exception e) {
                // Something went wrong!
            }
        }
        return sq.validate().toString();

    }

    /**
     * 获取记录
     *
     * @param conn
     * @param queryJSObj
     * @param query_offset
     * @param query_limit
     * @return
     */
    public Future<RowSet<Row>> getRecords(SqlConnection conn, JsonObject queryJSObj, int query_offset, int query_limit) {
        Promise<RowSet<Row>> promise = Promise.promise();
        /***
         * get_records with queryJSObj
         */
        //取出满足条件的记录
        String sql = this.creaStrGetRecordsWithJson(queryJSObj, query_offset, query_limit);
        conn
        .preparedQuery(sql)
        .execute(ar2 -> {
            //Release the connection to the pool
            conn.close();
            if (ar2.succeeded()) {
//	        	logger.info("getRecords, query success: "+ ar2.result().rowCount());
                promise.complete(ar2.result());
            } else {
                logger.info("getRecords, query Failed");
                //这里就相当于继续向上抛出异常，用Promise来向上抛异常
                promise.fail(ar2.cause());
            }
        });
        return promise.future();
    }

    /**
     * 创建查询符合搜索条件记录 的sql语句
     *
     * @param queryJS
     * @param query_offset
     * @param query_limit
     * @return
     */
    public String creaStrGetRecordsWithJson(JsonObject queryJS, int query_offset, int query_limit) {
        // define SelectQuery object
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("goods_gather_area_code"), new CustomSql("site_code"), new CustomSql("freight"),
        new CustomSql("class_lv1_code"), new CustomSql("class_lv2_code"), new CustomSql("variety_code"),
        new CustomSql("aggregate_code"), new CustomSql("aggregate_name"), new CustomSql("supplier_profit_rate"), new CustomSql("maximum_rate"), new CustomSql("minmum_rate"),
        new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time")
        )
        .addCustomFromTable("goods_price_bid_parameter");

        //common part to add InCondition
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    //添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                // Something went wrong!
            }
        }
        return sq.validate().toString() + " order by id desc  limit " + query_limit + " offset " + query_offset;
    }

    /***
     * 此方法判断传入的供应链商品聚集区、比价销售地、聚合商品名是否已经在列表中存在
     * @param conn 连接
     * @param goods_gather_area_code 应链商品聚集区编码
     * @param site_code 比价销售地编码
     * @param aggregation_code 聚合商品名
     * @return
     */
    public Future<RowSet<Row>> get_Goods_Price_Bid_Parameter_ByCode(SqlConnection conn, String goods_gather_area_code, String site_code, String aggregation_code) {
        Promise<RowSet<Row>> promise = Promise.promise();
        String sql = "select * from goods_price_bid_parameter where goods_gather_area_code = $1 and site_code = $2 and aggregate_code = $3 order by id desc limit 1";
        conn.preparedQuery(sql)
        .execute(Tuple.of(goods_gather_area_code,site_code,aggregation_code), ar -> {
            conn.close();
            if (ar.succeeded()) {
                logger.info("get_Goods_Price_Bid_Parameter_ByCode, 记录数: ", ar.result().rowCount());
                System.out.print(ar.result().rowCount()+"%&%&%&%&");
                promise.complete(ar.result());
            } else {
                logger.info("get_Goods_Price_Bid_Parameter_ByCode, 失败,失败原因:{}", ar.cause().getMessage());
                //这里就相当于继续向上抛出异常，用Promise来向上抛异常
                promise.fail(ar.cause());
            }
        });
        return promise.future();
    }

    /***
     *
     * @param conn
     * @param goods_gather_area_code
     * @param site_code
     * @param freight
     * @param class_lv1_code
     * @param class_lv2_code
     * @param variety_code
     * @param aggregation_code
     * @param aggregation_name
     * @param supplier_profit_rate
     * @param maximum_rate
     * @param minmum_rate
     * @param create_user
     * @return
     */
    public Future<RowSet<Row>> insertGoodsPriceBidParameter(SqlConnection conn, String goods_gather_area_code, String site_code, float freight,
                                                            String class_lv1_code, String class_lv2_code, String variety_code, String aggregation_code, String aggregation_name,
                                                            float supplier_profit_rate, float maximum_rate, float minmum_rate,
                                                            String create_user) {
        Promise<RowSet<Row>> promise = Promise.promise();
//        int is_delete = 0;
        String sql = "INSERT INTO goods_price_bid_parameter(goods_gather_area_code,site_code,freight,class_lv1_code, class_lv2_code, " +
        "variety_code, aggregate_code,aggregate_name,supplier_profit_rate,maximum_rate,minmum_rate,create_user)"
        + " VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)  RETURNING id";
        conn.preparedQuery(sql)
        .execute(Tuple.of(goods_gather_area_code, site_code, freight, class_lv1_code, class_lv2_code, variety_code, aggregation_code, aggregation_name, supplier_profit_rate, maximum_rate, minmum_rate, create_user), ar2 -> {
            //释放一个连接到连接池
            conn.close();
            if (ar2.succeeded()) {
                logger.info("insertgoods_price_bid_parameter, 插入记录数:{} ", ar2.result().rowCount());
                promise.complete(ar2.result());
            } else {
                logger.info("insertgoods_price_bid_parameter, 插入失败,失败原因:{}", ar2.cause().getMessage());
                //用Promise来向上抛异常
                promise.fail(ar2.cause());
            }
        });
        return promise.future();
    }


    /***
     * 根据id修改品种商品比价基础参数信息
     * @param conn
     * @param id
     * @param goods_gather_area_code
     * @param site_code
     * @param freight
     * @param class_lv1_code
     * @param class_lv2_code
     * @param variety_code
     * @param aggregation_code
     * @param aggregation_name
     * @param supplier_profit_rate
     * @param maximum_rate
     * @param minmum_rate
     * @param update_user
     * @return
     */
    public Future<RowSet<Row>> updateGoodsPriceBidParameterById(SqlConnection conn, long id, String goods_gather_area_code, String site_code, float freight,
                                                                String class_lv1_code, String class_lv2_code, String variety_code, String aggregation_code, String aggregation_name,
                                                                float supplier_profit_rate, float maximum_rate, float minmum_rate,
                                                                String update_user) {
        Promise<RowSet<Row>> promise = Promise.promise();
        String sql = "UPDATE goods_price_bid_parameter SET goods_gather_area_code = $1, site_code = $2,freight = $3,class_lv1_code = $4, class_lv2_code = $5, variety_code = $6, aggregate_code = $7,aggregate_name = $8," +
        "supplier_profit_rate= $9,maximum_rate= $10,minmum_rate= $11, update_user = $12 WHERE id = $13 RETURNING id";
        conn.preparedQuery(sql).execute(Tuple.of(goods_gather_area_code, site_code, freight, class_lv1_code, class_lv2_code, variety_code,
        aggregation_code, aggregation_name, supplier_profit_rate, maximum_rate, minmum_rate, update_user, id), ar -> {
            // Release the connection to the pool
            conn.close();
            if (ar.succeeded()) {
                logger.info("updateGoodsPriceBidParameterById, 更新记录数: {}", ar.result().rowCount());
                promise.complete(ar.result());
            } else {
                logger.info("updateGoodsPriceBidParameterById, 更新失败, 失败原因: {}", ar.cause().getMessage());
                // 这里就相当于继续向上抛出异常，用Promise来向上抛异常
                promise.fail(ar.cause());
            }
        });
        return promise.future();
    }

    /***
     *
     * @param conn
     * @param goods_gather_area_code
     * @param site_code
     * @param freight
     * @param class_lv1_code
     * @param class_lv2_code
     * @param variety_code
     * @param aggregation_code
     * @param aggregation_name
     * @param supplier_profit_rate
     * @param maximum_rate
     * @param minmum_rate
     * @param update_user
     * @return
     */
    public Future<RowSet<Row>> updateGoodsPriceBidParameterBysite_lv1_aggregation(SqlConnection conn, String goods_gather_area_code, String site_code, float freight,
                                                                                  String class_lv1_code, String class_lv2_code, String variety_code, String aggregation_code, String aggregation_name,
                                                                                  float supplier_profit_rate, float maximum_rate, float minmum_rate, String update_user) {
        Promise<RowSet<Row>> promise = Promise.promise();
        int is_delete = 0;
        String sql = "update goods_price_bid_parameter set goods_gather_area_code = $1, site_code = $2,freight = $3,class_lv1_code = $4, class_lv2_code = $5, variety_code = $6, aggregate_code = $7,aggregate_name = $8," +
        "supplier_profit_rate= $9,maximum_rate= $10,minmum_rate= $11, is_delete = $12 ,update_user = $13 WHERE goods_gather_area_code = $14 and site_code = $15 and aggregate_code = $16 RETURNING id";
        conn.preparedQuery(sql).execute(Tuple.of(goods_gather_area_code, site_code, freight, class_lv1_code, class_lv2_code, variety_code,
        aggregation_code, aggregation_name, supplier_profit_rate, maximum_rate, minmum_rate, is_delete, update_user, goods_gather_area_code, site_code, aggregation_code), ar -> {
            // Release the connection to the pool
            conn.close();
            if (ar.succeeded()) {
                logger.info("updateGoodsPriceBidParameterBysite_lv1_aggregation, 更新记录数: {}", ar.result().rowCount());
                System.out.print(ar.result().rowCount()+"&*&*&*&*");
                promise.complete(ar.result());
            } else {
                logger.info("updateGoodsPriceBidParameterBysite_lv1_aggregation, 更新失败, 失败原因: {}", ar.cause().getMessage());
                // 这里就相当于继续向上抛出异常，用Promise来向上抛异常
                promise.fail(ar.cause());
            }
        });
        return promise.future();
    }

    public Future<RowSet<Row>> deleteGoodsPriceBidParameter(SqlConnection conn, int is_delete, String id_list, String update_user) {
        Promise<RowSet<Row>> promise = Promise.promise();
        String sql = "update goods_price_bid_parameter set is_delete = $1, update_user = $2 where id = any(array" + id_list + ") RETURNING id";
        conn
        .preparedQuery(sql)
        .execute(Tuple.of(is_delete, update_user), ar -> {
            conn.close();
            if (ar.succeeded()) {
                logger.info("deleteGoodsPriceBidParameter, 逻辑删除记录数:{} ", ar.result().rowCount());
                promise.complete(ar.result());
            } else {
                logger.info("deleteGoodsPriceBidParameter, 逻辑删除失败,失败原因:{}", ar.cause().getMessage());
                //用Promise来向上抛异常
                promise.fail(ar.cause());
            }
        });
        return promise.future();
    }

    public Future<RowSet<Row>> deleteGoodsPriceBidParameterById(SqlConnection conn, int is_delete, long id, String update_user) {
        Promise<RowSet<Row>> promise = Promise.promise();
        String sql = "update goods_price_bid_parameter set is_delete = $1, update_user = $2 where id = $3 RETURNING id";
        conn
        .preparedQuery(sql)
        .execute(Tuple.of(is_delete, update_user, id), ar -> {
            conn.close();
            if (ar.succeeded()) {
                logger.info("deleteGoodsPriceBidParameter, 逻辑删除记录数:{} ", ar.result().rowCount());
                promise.complete(ar.result());
            } else {
                logger.info("deleteGoodsPriceBidParameter, 逻辑删除失败,失败原因:{}", ar.cause().getMessage());
                //用Promise来向上抛异常
                promise.fail(ar.cause());
            }
        });
        return promise.future();
    }


}
